package com.foosun.core.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

import jxl.Cell;
import jxl.CellType;
import jxl.LabelCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.apache.commons.lang.StringUtils;

public class ExcelUtils {
	
	
	
    /**
     * 读取excel到list
     *
     * @param filePath
     * @return
     * @throws java.io.FileNotFoundException
     */
    public static List<List<TreeMap<String, String>>> readExcel(String filePath) throws FileNotFoundException {
        InputStream is = new FileInputStream(filePath);
        List<List<TreeMap<String, String>>> al = readExcel(is);
        try {
            is.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return al;
    }

    public static TreeMap<String, String> readExcelToMap(String filePath) throws FileNotFoundException {
        InputStream is = new FileInputStream(filePath);
        TreeMap<String, String> al = readExcelToMap(is);
        try {
            is.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return al;
    }

    public static List<List<TreeMap<String, String>>> readExcel(InputStream is) {
        List<List<TreeMap<String, String>>> sheetList = new ArrayList<List<TreeMap<String, String>>>();// 取得整个xls各个sheet中的总数据
        try {

            Workbook rwb = Workbook.getWorkbook(is);
            Sheet[] sheets = rwb.getSheets();

            for (int i = 0; i < sheets.length; i++) {// 取得表中sheet的个数
                List<TreeMap<String, String>> list = new ArrayList<TreeMap<String, String>>();// 取得一个sheet中n行的总记录，里面是每行的记录
                TreeMap<String, String> sheetName = new TreeMap<String, String>();
                sheetName.put(String.valueOf(i), sheets[i].getName());
                list.add(sheetName);
                for (int k = 0; k < sheets[i].getRows(); k++) {// 读取多少行
                    TreeMap<String, String> map = new TreeMap<String, String>();
                    for (int j = 0; j < sheets[i].getColumns(); j++) {// 取得一行的列数
                        Cell cell = sheets[i].getCell(j, k);// 读出每行的每个单元格数据
                        String cellStr = cell.getContents();// 得到单元格的内容
                        if (cell.getType() == CellType.LABEL) {
                            LabelCell ctr = (LabelCell) cell;
                            cellStr = ctr.getString();
                        }
                        //map.put(sheets[i].getCell(j, k).getContents(), cellStr);
                        map.put(i + "-" + k + "-" + j, cellStr);
                        //System.err.println(sheets[i].getCell(j, k).getContents());
                    }
                    list.add(map);
                }
                sheetList.add(list);

            }
            // 关闭
            rwb.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return sheetList;
    }

    public static TreeMap<String, String> readExcelToMap(InputStream is) {
        TreeMap<String, String> dataMap = new TreeMap<String, String>();
        try {

            Workbook rwb = Workbook.getWorkbook(is);
            Sheet[] sheets = rwb.getSheets();

            for (int i = 0; i < sheets.length; i++) {// 取得表中sheet的个数
                dataMap.put(String.valueOf(i), sheets[i].getName());
                for (int k = 0; k < sheets[i].getRows(); k++) {// 读取多少行
                    for (int j = 0; j < sheets[i].getColumns(); j++) {// 取得一行的列数
                        Cell cell = sheets[i].getCell(j, k);// 读出每行的每个单元格数据
                        String cellStr = cell.getContents();// 得到单元格的内容
                        if (cell.getType() == CellType.LABEL) {
                            LabelCell ctr = (LabelCell) cell;
                            cellStr = ctr.getString();
                        }
                        if (StringUtils.isNotBlank(cellStr)) {
                            dataMap.put(i + "-" + k + "-" + j, cellStr);
                        }
                    }
                }

            }
            // 关闭
            rwb.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return dataMap;
    }

    public static void writeExcel(String filePath, String title, Collection<String> heads, List<Object[]> content) throws Exception {
        // 打开文件
        File file = new File(filePath);
        if (!file.exists()) {
            file.createNewFile();
        }
        WritableWorkbook book = Workbook.createWorkbook(file);
        WritableSheet sheet = book.createSheet(title, 0);
        int i = 0, j = 0;
        for (String head : heads) {
            sheet.addCell(new Label(j++, i, head));
        }
        for (Object[] list : content) {
            i++;
            j = 0;
            for (Object object : list) {
                sheet.addCell(new Label(j++, i, String.valueOf(object)));
            }
        }
        book.write();
        book.close();

    }
    

    public static void writeExcelByMap(String filePath, Map<String, Object> dataMap) throws Exception {
        // 打开文件
        File file = new File(filePath);
        if (!file.exists()) {
            file.createNewFile();
        }
        WritableWorkbook book = Workbook.createWorkbook(file);
        Map<String, WritableSheet> sheetMap = new HashMap<String, WritableSheet>();
        for (String key : dataMap.keySet()) {
            String xyz[] = key.split("-");
            String x = xyz[0], y = "", z = "";
            if (xyz.length == 3) {
                y = xyz[1];
                z = xyz[2];
            }

            WritableSheet sheet = sheetMap.get(x);
            if (sheet == null) {
                String title = dataMap.get(x) == null ? "未名命" : dataMap.get(x).toString();
                sheet = book.createSheet(title, Integer.valueOf(x));
                sheetMap.put(x, sheet);
            }
            if (StringUtils.isNotBlank(z)) {
                sheet.addCell(new Label(Integer.valueOf(z), Integer.valueOf(y), String.valueOf(dataMap.get(key))));
            }
        }

        book.write();
        book.close();

    }

    public static void main(String[] args) throws Exception {
//        FileInputStream is = new FileInputStream("E:\\student.xls");
//        Workbook rwb = Workbook.getWorkbook(is);
//        Sheet[] sheets = rwb.getSheets();
//        int sheetIndex = 0;
//        System.out.println("sheet:" + sheets[sheetIndex].getName());
        ExcelUtils.readTest();

//        for (int k = 1; k < sheets[sheetIndex].getRows(); k++) {// 读取多少行
//            Cell cell = sheets[sheetIndex].getCell(4, k);
//            String name = cell.getContents();
//            System.out.println(name);
//
//            System.out.println("========================");
//        }
    }

    public static void readTest() throws FileNotFoundException {
        List<List<TreeMap<String, String>>> out = ExcelUtils.readExcel("E:\\student.xls");
        for (List<TreeMap<String, String>> list : out) {
            for (TreeMap<String, String> treeMap : list) {
                for (String key : treeMap.keySet()) {
                    System.out.println(key + ":" + treeMap.get(key));
                }
            }
        }
    }
}
